# -------------------------
# Settings
# -------------------------
from flask import Flask, render_template, request, redirect, url_for, jsonify, abort, send_from_directory
from database.db import fetch_all, fetch_one, execute, transaction
from database.config import SECRET_KEY
from datetime import datetime, date
from dateutil.parser import parse as parse_date
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = SECRET_KEY
app.template_folder = os.path.join(app.root_path, 'models', 'templates')
app.static_folder = os.path.join(app.root_path, 'models', 'static')

# -------------------------
# Helpers
# -------------------------
def _normalize_gender(value):
    """Normalize various gender inputs to 'M' or 'F' (database expects 'M'/'F')."""
    if value is None:
        return None
    v = str(value).strip()
    # Support Chinese
    mapping = {
        '男': 'M', '女': 'F',
        'm': 'M', 'f': 'F',
        'M': 'M', 'F': 'F',
        'male': 'M', 'female': 'F',
        'Male': 'M', 'Female': 'F'
    }
    return mapping.get(v, v)

def validate_patient_payload(data):
    """
    Validate patient payload from form or JSON.
    Returns sanitized dict for SQL.
    Raises ValueError on validation error.
    """
    name = data.get('name')
    if not name or str(name).strip() == '':
        raise ValueError("Name is a required field")
    gender = _normalize_gender(data.get('gender'))
    if gender not in ('M', 'F'):
        raise ValueError("Gender must be M or F (also accepts 男/女)")
    raw_age = data.get('age')
    if raw_age is None or str(raw_age).strip() == '':
        raise ValueError("Age is a required field")
    try:
        age = int(raw_age)
        if age <= 0:
            raise ValueError("Age must be greater than 0")
    except Exception:
        raise ValueError("Age must be a positive integer")
    phone = data.get('phone')
    if not phone or str(phone).strip() == '':
        raise ValueError("Phone number is a required field")
    email = data.get('email') or None
    address = data.get('address') or None
    medical_history = data.get('medical_history') or None

    return {
        'name': str(name).strip(),
        'gender': gender,
        'age': age,
        'phone': str(phone).strip(),
        'email': email.strip() if email else None,
        'address': address.strip() if address else None,
        'medical_history': medical_history.strip() if medical_history else None
    }

def paginate_query(query_base, params, page, per_page):
    offset = (page - 1) * per_page
    return fetch_all(query_base + " LIMIT %s OFFSET %s", params + [per_page, offset])

# -------------------------
# HTML ROUTES
# -------------------------
@app.route('/')
def home():
    return render_template('home.html')

# Patients: list, new, edit, delete
@app.route('/patients', methods=['GET', 'POST'])
def patients_page():
    error = None
    if request.method == 'POST':
        data = request.form.to_dict()
        try:
            payload = validate_patient_payload(data)
            execute("""
                INSERT INTO patient (name, gender, age, phone, email, address, medical_history)
                VALUES (%s,%s,%s,%s,%s,%s,%s)
            """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
                  payload['email'], payload['address'], payload['medical_history']))
            # Redirect to GET page after submission
            return redirect(url_for('patients_page'))
        except ValueError as ve:
            error = str(ve)
        except Exception as e:
            error = str(e)
    # GET rendering
    patients = fetch_all("SELECT * FROM patient ORDER BY patient_id DESC LIMIT 500;")
    return render_template('patient_form.html', patients=patients, error=error)

@app.route('/patients/new', methods=['GET', 'POST'])
def patient_new():
    if request.method == 'POST':
        try:
            payload = validate_patient_payload(request.form)
            execute("""
                INSERT INTO patient (name, gender, age, phone, email, address, medical_history)
                VALUES (%s,%s,%s,%s,%s,%s,%s)
            """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
                  payload['email'], payload['address'], payload['medical_history']))
            return redirect(url_for('patients_page'))
        except Exception as e:
            return render_template('patient_form.html', error=str(e), patient=request.form)
    return render_template('patient_form.html', patient=None, error=None)

@app.route('/patients/edit/<int:patient_id>', methods=['GET', 'POST'])
def patient_edit(patient_id):
    if request.method == 'POST':
        try:
            payload = validate_patient_payload(request.form)
            execute("""
                UPDATE patient SET name=%s, gender=%s, age=%s, phone=%s, email=%s, address=%s, medical_history=%s
                WHERE patient_id=%s
            """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
                  payload['email'], payload['address'], payload['medical_history'], patient_id))
            return redirect(url_for('patients_page'))
        except Exception as e:
            return render_template('patient_form.html', error=str(e), patient=request.form)
    patient = fetch_one("SELECT * FROM patient WHERE patient_id=%s", (patient_id,))
    if not patient:
        abort(404, "Patient not found")
    return render_template('patient_form.html', patient=patient, error=None)

@app.route('/patients/delete/<int:patient_id>', methods=['POST'])
def patient_delete(patient_id):
    try:
        execute("DELETE FROM patient WHERE patient_id=%s", (patient_id,))
        return redirect(url_for('patients_page'))
    except Exception as e:
        return str(e), 500

# Staff CRUD
@app.route('/staff')
def staff_page():
    staff = fetch_all("SELECT s.*, d.name as department_name FROM staff s LEFT JOIN department d ON s.dept_id=d.dept_id ORDER BY s.staff_id;")
    depts = fetch_all("SELECT dept_id, name FROM department ORDER BY dept_id;")
    return render_template('staff.html', staff=staff, depts=depts)

@app.route('/staff/new', methods=['GET', 'POST'])
def staff_new():
    if request.method == 'POST':
        form = request.form
        try:
            name = form.get('name'); role = form.get('role'); phone = form.get('phone')
            if not (name and role and phone):
                raise ValueError("Name/Role/Phone are required fields")
            gender = _normalize_gender(form.get('gender'))
            age = int(form.get('age')) if form.get('age') else None
            execute("""
                INSERT INTO staff (name, gender, age, role, specialty, phone, email, dept_id)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
            """, (name, gender, age, role, form.get('specialty'), phone, form.get('email'), form.get('dept_id') or None))
            return redirect(url_for('staff_page'))
        except Exception as e:
            depts = fetch_all("SELECT dept_id, name FROM department ORDER BY dept_id;")
            return render_template('staff_form.html', error=str(e), form=form, depts=depts)
    depts = fetch_all("SELECT dept_id, name FROM department ORDER BY dept_id;")
    return render_template('staff_form.html', depts=depts, form=None, error=None)

@app.route('/staff/edit/<int:staff_id>', methods=['GET', 'POST'])
def staff_edit(staff_id):
    if request.method == 'POST':
        form = request.form
        try:
            execute("""
                UPDATE staff SET name=%s, gender=%s, age=%s, role=%s, specialty=%s, phone=%s, email=%s, dept_id=%s
                WHERE staff_id=%s
            """, (form.get('name'), _normalize_gender(form.get('gender')), form.get('age') or None, form.get('role'),
                  form.get('specialty'), form.get('phone'), form.get('email'), form.get('dept_id') or None, staff_id))
            return redirect(url_for('staff_page'))
        except Exception as e:
            depts = fetch_all("SELECT dept_id, name FROM department ORDER BY dept_id;")
            return render_template('staff_form.html', error=str(e), form=form, depts=depts)
    staff = fetch_one("SELECT * FROM staff WHERE staff_id=%s", (staff_id,))
    if not staff:
        abort(404)
    depts = fetch_all("SELECT dept_id, name FROM department ORDER BY dept_id;")
    return render_template('staff_form.html', depts=depts, form=staff, error=None)

@app.route('/staff/delete/<int:staff_id>', methods=['POST'])
def staff_delete(staff_id):
    try:
        execute("DELETE FROM staff WHERE staff_id=%s", (staff_id,))
        return redirect(url_for('staff_page'))
    except Exception as e:
        return str(e), 500

# Appointments: list, new (GET/POST), cancel (POST)
@app.route('/appointments')
def appointments_page():
    appts = fetch_all("""
        SELECT a.*, p.name AS patient_name, s.name AS staff_name, d.name AS dept_name
        FROM appointment a
        JOIN patient p ON a.patient_id = p.patient_id
        JOIN staff s ON a.staff_id = s.staff_id
        LEFT JOIN department d ON s.dept_id = d.dept_id
        ORDER BY a.appointment_date DESC
        LIMIT 500
    """)
    patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
    staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
    return render_template('appointments.html', appointments=appts, patients=patients, staff=staff, error=None)

@app.route('/appointments/new', methods=['GET', 'POST'])
def appointment_new():
    if request.method == 'POST':
        form = request.form
        try:
            patient_id = int(form['patient_id'])
            staff_id = int(form['staff_id'])
            appointment_date = parse_date(form['appointment_date']).date()
            def fn(cur):
                cur.execute("""
                    SELECT COUNT(*) AS cnt FROM appointment
                    WHERE staff_id=%s AND appointment_date=%s
                """, (staff_id, appointment_date))
                cnt = cur.fetchone().get('cnt', 0)
                if cnt and int(cnt) > 0:
                    raise ValueError("This doctor already has an appointment on this date")
                cur.execute("""
                    INSERT INTO appointment (patient_id, staff_id, appointment_date, status)
                    VALUES (%s,%s,%s,%s)
                """, (patient_id, staff_id, appointment_date, form.get('status', 'Scheduled')))
                return True
            transaction(fn)
            return redirect(url_for('appointments_page'))
        except Exception as e:
            patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
            staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
            return render_template('appointments.html', appointments=fetch_all("SELECT * FROM appointment ORDER BY appointment_date DESC LIMIT 100"), patients=patients, staff=staff, error=str(e))
    # GET render
    patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
    staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
    return render_template('appointments_form.html', patients=patients, staff=staff, error=None)

@app.route('/appointments/cancel/<int:appointment_id>', methods=['POST'])
def appointment_cancel(appointment_id):
    try:
        execute("UPDATE appointment SET status='Cancelled' WHERE appointment_id=%s", (appointment_id,))
        return redirect(url_for('appointments_page'))
    except Exception as e:
        return str(e), 500

# Medical records: list, add
@app.route('/medical_records')
def medical_records_page():
    recs = fetch_all("""
        SELECT mr.*, p.name AS patient_name, s.name AS staff_name
        FROM medicalrecord mr
        JOIN patient p ON mr.patient_id = p.patient_id
        LEFT JOIN staff s ON mr.staff_id = s.staff_id
        ORDER BY mr.visit_date DESC LIMIT 500
    """)
    patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
    staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
    return render_template('medical_records.html', records=recs, patients=patients, staff=staff, error=None)

@app.route('/medical_records/new', methods=['GET', 'POST'])
def medical_record_new():
    if request.method == 'POST':
        form = request.form
        try:
            patient_id = int(form['patient_id'])
            staff_id = int(form['staff_id']) if form.get('staff_id') else None
            execute("""
                INSERT INTO medicalrecord (patient_id, staff_id, visit_date, diagnosis, symptoms, observation)
                VALUES (%s,%s,%s,%s,%s,%s)
            """, (patient_id, staff_id, datetime.today().date(), form.get('diagnosis'), form.get('symptoms'), form.get('observation')))
            return redirect(url_for('medical_records_page'))
        except Exception as e:
            patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
            staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
            return render_template('medical_record_form.html', patients=patients, staff=staff, error=str(e))
    patients = fetch_all("SELECT patient_id, name FROM patient ORDER BY patient_id")
    staff = fetch_all("SELECT staff_id, name FROM staff ORDER BY staff_id")
    return render_template('medical_record_form.html', patients=patients, staff=staff, error=None)

# Medicines list/create
@app.route('/medicines')
def medicines_page():
    meds = fetch_all("SELECT * FROM medicine ORDER BY medicine_id")
    return render_template('medicines.html', medicines=meds, error=None)

@app.route('/medicines/new', methods=['GET', 'POST'])
def medicine_new():
    if request.method == 'POST':
        form = request.form
        try:
            name = form.get('name')
            dosage = form.get('dosage')
            stock = int(form.get('stock', 0))
            if stock < 0:
                raise ValueError("Stock cannot be negative")
            execute("INSERT INTO medicine (name, dosage, stock) VALUES (%s,%s,%s)", (name, dosage, stock))
            return redirect(url_for('medicines_page'))
        except Exception as e:
            return render_template('medicine_form.html', form=form, error=str(e))
    return render_template('medicine_form.html', form=None, error=None)

# Departments, wards, invoices simple pages
@app.route('/departments')
def departments_page():
    depts = fetch_all("SELECT d.*, (SELECT COUNT(*) FROM staff s WHERE s.dept_id=d.dept_id) AS staff_count FROM department d ORDER BY d.dept_id;")
    return render_template('departments.html', depts=depts)

@app.route('/wards')
def wards_page():
    wards = fetch_all("SELECT * FROM ward ORDER BY ward_id;")
    return render_template('wards.html', wards=wards)

@app.route('/invoices')
def invoices_page():
    invoices = fetch_all("""
       SELECT i.*, p.name AS patient_name
       FROM invoice i JOIN patient p ON i.patient_id = p.patient_id
       ORDER BY i.issued_date DESC LIMIT 500
    """)
    return render_template('invoices.html', invoices=invoices)

# -------------------------
# Error handlers (HTML)
# -------------------------
@app.errorhandler(404)
def page_not_found(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'not found'}), 404
    return render_template('404.html', message=str(e)), 404

@app.errorhandler(400)
def bad_request(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'bad request', 'message': str(e)}), 400
    return str(e), 400

@app.errorhandler(500)
def server_error(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'server error', 'message': str(e)}), 500
    return str(e), 500

# -------------------------
# Web Icon 
# -------------------------
@app.route('/favicon.ico')
def favicon():
    return send_from_directory(os.path.join(app.root_path, 'static'),
                               'favicon.ico', mimetype='favicon.ico')


# -------------------------
# REST API (JSON)
# -------------------------

# Patients endpoints (pagination & search)
@app.route('/api/patients', methods=['GET'])
def api_patients_list():
    page = int(request.args.get('page', 1))
    per_page = int(request.args.get('per_page', 20))
    name = request.args.get('name')
    if name:
        base = "SELECT * FROM patient WHERE name ILIKE %s ORDER BY patient_id"
        params = [f"%{name}%"]
        rows = paginate_query(base, params, page, per_page)
        return jsonify(rows), 200
    base = "SELECT * FROM patient ORDER BY patient_id"
    rows = paginate_query(base, [], page, per_page)
    return jsonify(rows), 200

@app.route('/api/patients/<int:pid>', methods=['GET'])
def api_patient_get(pid):
    row = fetch_one("SELECT * FROM patient WHERE patient_id=%s", (pid,))
    if not row:
        return jsonify({'error': 'not found'}), 404
    return jsonify(row), 200

@app.route('/api/patients', methods=['POST'])
def api_patient_create():
    data = request.get_json()
    try:
        payload = validate_patient_payload(data)
        execute("""
            INSERT INTO patient (name, gender, age, phone, email, address, medical_history)
            VALUES (%s,%s,%s,%s,%s,%s,%s)
        """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
              payload['email'], payload['address'], payload['medical_history']))
        return jsonify({'message': 'created'}), 201
    except ValueError as ve:
        return jsonify({'error': str(ve)}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/patients/<int:pid>', methods=['PUT'])
def api_patient_update(pid):
    data = request.get_json()
    try:
        payload = validate_patient_payload(data)
        execute("""
            UPDATE patient SET name=%s, gender=%s, age=%s, phone=%s, email=%s, address=%s, medical_history=%s
            WHERE patient_id=%s
        """, (payload['name'], payload['gender'], payload['age'], payload['phone'],
              payload['email'], payload['address'], payload['medical_history'], pid))
        return jsonify({'message': 'updated'}), 200
    except ValueError as ve:
        return jsonify({'error': str(ve)}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/patients/<int:pid>', methods=['DELETE'])
def api_patient_delete(pid):
    try:
        execute("DELETE FROM patient WHERE patient_id=%s", (pid,))
        return jsonify({'message': 'deleted'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# Appointments available (list staff free on date)
@app.route('/api/appointments/available', methods=['GET'])
def api_appointments_available():
    date_str = request.args.get('Date')
    if not date_str:
        return jsonify({'error': 'missing date param'}), 400
    try:
        dt = parse_date(date_str).date()
    except Exception:
        return jsonify({'error': 'invalid date format'}), 400
    rows = fetch_all("""
        SELECT staff_id, name FROM staff
        WHERE staff_id NOT IN (
            SELECT staff_id FROM appointment WHERE appointment_date=%s
        )
    """, (dt,))
    return jsonify(rows), 200

# Appointments details (complex join)
@app.route('/api/appointments/details', methods=['GET'])
def api_appointments_details():
    rows = fetch_all("""
        SELECT a.appointment_id, a.appointment_date, a.status,
               p.name AS patient_name, s.name AS staff_name, d.name AS department_name
        FROM appointment a
        INNER JOIN patient p ON a.patient_id = p.patient_id
        LEFT JOIN staff s ON a.staff_id = s.staff_id
        LEFT JOIN department d ON s.dept_id = d.dept_id
        ORDER BY a.appointment_date DESC
        LIMIT 1000
    """)
    return jsonify(rows), 200

# Departments revenue (complex WITH + joins + group by)
@app.route('/api/departments/revenue', methods=['GET'])
def api_departments_revenue():
    rows = fetch_all("""
        WITH latest_record AS (
            SELECT DISTINCT ON (patient_id) patient_id, staff_id
            FROM medicalrecord
            ORDER BY patient_id, visit_date DESC
        )
        SELECT d.dept_id, d.name AS department_name, COALESCE(SUM(i.amount),0) AS total_amount
        FROM department d
        LEFT JOIN staff s ON s.dept_id = d.dept_id
        LEFT JOIN latest_record lr ON lr.staff_id = s.staff_id
        LEFT JOIN invoice i ON i.patient_id = lr.patient_id
        GROUP BY d.dept_id, d.name
        HAVING COALESCE(SUM(i.amount),0) >= 0
        ORDER BY total_amount DESC
    """)
    return jsonify(rows), 200

# Doctors busy (count appointments in a month)
@app.route('/api/doctors/busy', methods=['GET'])
def api_doctors_busy():
    month = request.args.get('month')
    threshold = int(request.args.get('threshold', 0))
    if not month:
        return jsonify({'error': 'missing month param e.g. 2025-10'}), 400
    try:
        start = parse_date(month + "-01").date()
    except Exception:
        return jsonify({'error': 'invalid month format'}), 400
    if start.month < 12:
        end = start.replace(month=start.month+1, day=1)
    else:
        end = start.replace(year=start.year+1, month=1, day=1)
    rows = fetch_all("""
        SELECT s.staff_id, s.name, COUNT(a.appointment_id) AS cnt
        FROM staff s
        LEFT JOIN appointment a ON a.staff_id=s.staff_id
          AND a.appointment_date >= %s AND a.appointment_date < %s
        GROUP BY s.staff_id, s.name
        HAVING COUNT(a.appointment_id) > %s
        ORDER BY cnt DESC
    """, (start, end, threshold))
    return jsonify(rows), 200

# Patients with unpaid invoices
@app.route('/api/patients/unpaid', methods=['GET'])
def api_patients_unpaid():
    rows = fetch_all("""
        SELECT DISTINCT p.*
        FROM patient p
        JOIN invoice i ON i.patient_id = p.patient_id
        WHERE i.status = 'Unpaid' OR i.status = 'Unpaid'
    """)
    return jsonify(rows), 200

# -------------------------
# Error handlers (JSON fallback)
# -------------------------
@app.errorhandler(404)
def api_page_not_found(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'not found'}), 404
    return jsonify({'error': 'not found'}), 404

@app.errorhandler(400)
def api_bad_request(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'bad request', 'message': str(e)}), 400
    return jsonify({'error': 'bad request'}), 400

@app.errorhandler(500)
def api_server_error(e):
    if request.path.startswith('/api/'):
        return jsonify({'error': 'server error', 'message': str(e)}), 500
    return jsonify({'error': 'server error'}), 500
# -------------------------
# Run
# -------------------------
if __name__ == '__main__':
    app.run(debug=True)

